package cmpe.swe.purcom.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cmpe.swe.purcom.dao.mapper.UserRowMapper;
import cmpe.swe.purcom.model.User;

@Component
public class UserDao extends PcDao {

	/**
	 * Returns a User object that includes id properties. 
	 * <p>
	 * These properties are set by using PreparedStatementCreator() function inside the method.
	 * 
	 * @param u an User object which represents new user.
	 * @return return the new generated User object.
	 */
	public User createUser(final User u) {
		final String query = "INSERT INTO USER (EMAIL, PASSWORD, NAME, SURNAME, LOCATION, PROFILEPICLOC) VALUES(?,?,?,?,?,?)";

		KeyHolder gkh = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, u.getEmail());
				ps.setString(2, u.getPassword());
				ps.setString(3, u.getName());
				ps.setString(4, u.getSurname());
				ps.setString(5, u.getLocation());
				ps.setString(6, u.getProfilePicLoc());

				return ps;
			}
		}, gkh);

		Long newUserId = gkh.getKey().longValue();
		u.setId(newUserId);
		return u;
	}

	/**
	 * Returns a User object which corresponds to given e mail address. 
	 * <p>
	 * If the given e-mail address is not in the database, it will return null
	 * 
	 * @param email an e-mail address which is used for user identification
	 * @return the User object  
	 */
	public User getUserByEmail(String email) {
		List<User> users = this.getTemplate().query(
				"SELECT * FROM USER u WHERE u.email = ? ",
				new Object[] { email }, new UserRowMapper());

		if (users.isEmpty()) {
			return null;
		} else {
			return users.get(0);
		}

	}

	/**
	 * Returns a User object which includes skill properties. These properties are set
	 * by using cretaPreparedStatement() function inside the method 
	 * 
	 * @param u a User object which is wanted to attach a skill
	 * @return a User object with an attached skill
	 */
	public User attachSkillsToUser(final User u) {
		final String query = "INSERT INTO USER_SKILL (userId, skillId) VALUES(?,?)";
		
		Long[] skillIds = u.getSkillIds();

		KeyHolder gkh = new GeneratedKeyHolder();
		for (int i = 0; i < skillIds.length; i++) {
			final Long skillId = skillIds[i];
			this.getTemplate().update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(
						Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(query,
							Statement.RETURN_GENERATED_KEYS);
					ps.setLong(1, u.getId());
					ps.setLong(2, skillId);

					return ps;
				}
			}, gkh);

			//newItemIds[i] = gkh.getKey().longValue();
		}
		return u;
	}

}
